package cn.com.lms.business.exam.persistence.repository;

import cn.com.lms.business.exam.persistence.entity.PaperSubject;
import cn.com.lms.business.exam.persistence.entity.Question;
import cn.com.lms.business.exam.persistence.entity.QuestionSubject;

import com.bnzj.core.persistence.repository.BaseRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.util.List;
import java.util.Map;

@Repository
public interface PaperSubjectRepository extends BaseRepository<PaperSubject, Long> {

    /**
     * 查询试卷试题列表-固定题
     * @param paperIdList 试卷IdList
     * @return
     */
    @Query(value = "select ps.paper_id as paperId,ps.score,ps.sort_id as sortId,qs.question_id as  questionId," +
            " qs.type ,qs.id as subjectId,qs.subject,qs.correct_status as correctStatus,qs.is_delete as isDelete ,(select group_concat(CONCAT(option_name,\" \",content) SEPARATOR '###')   from bnz_exam_question_subject_option so LEFT JOIN bnz_exam_question_subject qs  on so.subject_id=qs.id   where so.subject_id=ps.subject_id " +
            " GROUP BY subject_id) options, " +
            " (select group_concat(CONCAT(file_path))   from bnz_exam_question_subject_file sf where sf.subject_id=ps.subject_id " +
            " GROUP BY subject_id) images "+
            " from bnz_exam_paper_subject ps LEFT JOIN bnz_exam_question_subject qs on ps.subject_id=qs.id " +
            " where ps.is_delete=0 and  ps.paper_id in :paperIdList order by ps.sort_id asc ", nativeQuery = true)
    List<Map<String,Object>> findQuestionSubjectByPaperIdList(@Param(value = "paperIdList") List<Long> paperIdList);

    /**
     * 查询试卷试总分
     * @param paperIdList 试卷IdList
     * @return
     */
    @Query(value = "select sum(ps.score) paperScore,ps.paper_id as paperId  from lms.bnz_exam_paper_subject ps LEFT JOIN lms.bnz_exam_question_subject qs on ps.subject_id=qs.id where ps.is_delete=0 and  ps.paper_id in :paperIdList GROUP BY ps.paper_id ", nativeQuery = true)
    List<Map> findPaperScoreByPaperIdList(@Param(value = "paperIdList") List<Long> paperIdList);


    /**
     * 查询试卷试题题库
     * @param paperId 试卷Id
     * @return
     */
    @Query(value = "select IFNULL(pq.single_count,0) as singleCount,IFNULL(pq.multiple_count,0) as multipleCount,IFNULL(pq.judge_count,0) as  judgeCount,IFNULL(pq.fill_count,0) as fillCount,IFNULL(pq.explain_count,0) as explainCount,q.question_name as questionName,q.id, (select count(1) from bnz_exam_question_subject qs where qs.question_id=q.id and qs.is_delete=0) as  count," +
            " (select count(1) from bnz_exam_question_subject qs where qs.type=1 and qs.question_id=q.id and qs.is_delete=0) as  totalSingleCount," +
            " (select count(1) from bnz_exam_question_subject qs where qs.type=2 and qs.question_id=q.id and qs.is_delete=0) as  totalMultipleCount," +
            " (select count(1) from bnz_exam_question_subject qs where qs.type=3 and qs.question_id=q.id and qs.is_delete=0) as  totalJudgeCount," +
            " (select count(1) from bnz_exam_question_subject qs where qs.type=4 and qs.question_id=q.id and qs.is_delete=0) as  totalFillCount," +
            " (select count(1) from bnz_exam_question_subject qs where qs.type=5 and qs.question_id=q.id and qs.is_delete=0) as  totalExplainCount from lms.bnz_exam_paper_question pq LEFT JOIN lms.bnz_exam_question q on pq.question_id=q.id where pq.is_delete=0 and  pq.paper_id=:paperId ", nativeQuery = true)
    List<Map> findQuestionByPaperId(@Param(value = "paperId") Long paperId);




    /**
     * 查询试卷题库中试题列表
     * @param paperId 试卷Id
     * @return
     */
    @Query(value = "select qs.question_id as  questionId, qs.type ,qs.id as subjectId,qs.subject,qs.correct_status as correctStatus," +
            "qs.is_delete as isDelete,(select group_concat(CONCAT(so.option_name,\" \",so.content) SEPARATOR '###')  from bnz_exam_question_subject_option so  where so.subject_id=qs.id GROUP BY so.subject_id) options " +
            "  from bnz_exam_question_subject qs  where qs.question_id in (select q.id from lms.bnz_exam_paper_question pq LEFT JOIN lms.bnz_exam_question q on pq.question_id=q.id where pq.paper_id=:paperId) ", nativeQuery = true)
    List<Map<String,Object>> findRandQuestionSubjectByPaperId(@Param(value = "paperId") Long paperId);


    /**
     * 固定试卷题目信息
     * @param subjectId
     * @return
     */
    @Query(value = "select ps.subject_id as subjectId,ps.score as subjectScore,ps.sort_id as sortId,qs.subject,qs.type,sa.answer as rightAnswer ,sa.parse " +
            " from bnz_exam_paper_subject ps left join bnz_exam_question_subject qs  on ps.subject_id=qs.id  " +
            "left  join bnz_exam_question_subject_answer sa on sa.subject_id=qs.id   " +
            " where ps.paper_id=?1 and ps.subject_id=?2", nativeQuery = true)
    Map findPaperSubjectByPaperIdAndSubjectId(Long paperId,Long subjectId);


    @Query(value = "select ps.subject_id as subjectId,ps.score as subjectScore,ps.sort_id as sortId,qs.subject,qs.type,sa.answer as rightAnswer ,sa.parse," +
            " (select group_concat(CONCAT(so.option_name,\" \",so.content) SEPARATOR '###')  from bnz_exam_question_subject_option so  where so.subject_id=qs.id GROUP BY so.subject_id) options, " +
            " (select group_concat(CONCAT(file_path))   from bnz_exam_question_subject_file sf where sf.subject_id=qs.id " +
            " GROUP BY subject_id) images"+
            " from bnz_exam_paper_subject ps left join bnz_exam_question_subject qs  on ps.subject_id=qs.id  " +
            " left  join bnz_exam_question_subject_answer sa on sa.subject_id=qs.id   " +
            " where ps.paper_id=?1 ", nativeQuery = true)
    List<Map<String,Object>> findPaperSubjectByPaperId(Long paperId);


    /**
     * 查询包含试题id的试卷
     * @param subjectIdList 题目IdList
     * @return
     */
    @Query(value = "select  ps.paper_id as paperId  from lms.bnz_exam_paper_subject ps  where ps.subject_id in :subjectIdList ", nativeQuery = true)
    List<Map> findPaperBySubjectIdList(@Param(value = "subjectIdList") List<Long> subjectIdList);

    @Transactional
    @Modifying
    @Query(value = "delete from bnz_exam_paper_subject where paper_id=?1", nativeQuery = true)
    public int deleteByPaperId(Long paperId);



    /**
     * 查询试卷试题列表-随机
     * @param paperIdList 试卷IdList
     * @return
     */
    @Query(value = "select ps.paper_id as paperId,ps.score,ps.sort_id as sortId,qs.question_id as  questionId," +
            " qs.type ,qs.id as subjectId,qs.subject,qs.correct_status as correctStatus,qs.is_delete as isDelete ,(select group_concat(CONCAT(option_name,\" \",content) SEPARATOR '###')   from bnz_exam_question_subject_option so LEFT JOIN bnz_exam_question_subject qs  on so.subject_id=qs.id   where so.subject_id=ps.subject_id " +
            " GROUP BY subject_id) options, " +
            " (select group_concat(CONCAT(file_path))   from bnz_exam_question_subject_file sf where sf.subject_id=ps.subject_id " +
            " GROUP BY subject_id) images "+
            " from bnz_exam_paper_subject ps LEFT JOIN bnz_exam_question_subject qs on ps.subject_id=qs.id " +
            " where qs.question_id is not null and  ps.is_delete=0 and ps.creator_name='system' and ps.creator= :employeeId and  ps.paper_id in :paperIdList order by ps.sort_id asc ", nativeQuery = true)
    List<Map<String,Object>> findRadomQuestionSubjectByPaperIdList(@Param(value = "employeeId") Long employeeId,@Param(value = "paperIdList") List<Long> paperIdList);

}